using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Dynamic;
using System.Data.SqlClient;
using System.Configuration;
using Model;
using Common;

namespace DAL
{
    public class NewsDAL
    {
        public static int Save(NewsInfo info)
        {
            string sql = string.Format("insert into [news]([Title],[FContent],[UserID],[Pageviews],[Praise],[Contact],[Phone],[Company],[Province],[City],[Area],[CoverImg],[CreateDate]) values(@Title,@FContent,@UserID,@Pageviews,@Praise,@Contact,@Phone,@Company,@Province,@City,@Area,@CoverImg,@CreateDate);select @@identity;");
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@Title", info.Title),
                new SqlParameter("@FContent", info.FContent),
                new SqlParameter("@UserID", info.UserID),
                new SqlParameter("@Pageviews", info.Pageviews),
                new SqlParameter("@Praise", info.Praise),
                new SqlParameter("@Contact", info.Contact),
                new SqlParameter("@Phone", info.Phone),
                new SqlParameter("@Company", info.Company),
                new SqlParameter("@Province", info.Province),
                new SqlParameter("@City", info.City),
                new SqlParameter("@Area", info.Area),
                new SqlParameter("@CoverImg", info.CoverImg),
                new SqlParameter("@CreateDate", info.CreateDate)
            };
            return DataConvert.ToInt(CommonDAL.ExecuteScalar2(sql, paras));
        }
        public static int Update(NewsInfo info)
        {
            string sql = string.Format("update [news] set [Title]=@Title,[Pageviews]=@Pageviews,[Praise]=@Praise,[Phone]=@Phone,[Province]=@Province,[Area]=@Area,[City]=@City,[Company]=@Company,[Contact]=@Contact,[CoverImg]=@CoverImg,[FContent]=@FContent where [ID]=@ID");
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@Title", info.Title),
                new SqlParameter("@Pageviews", info.Pageviews),
                new SqlParameter("@Praise", info.Praise),
                new SqlParameter("@Phone", info.Phone),
                new SqlParameter("@Province", info.Province),
                new SqlParameter("@Area", info.Area),
                new SqlParameter("@City", info.City),
                new SqlParameter("@Company", info.Company),
                new SqlParameter("@Contact", info.Contact),
                new SqlParameter("@CoverImg", info.CoverImg),
                new SqlParameter("@FContent", info.FContent),
                new SqlParameter("@ID", info.ID)
            };
            return CommonDAL.ExecuteNoneQuery(sql, paras) ? info.ID : 0;
        }
        public static int Update_PageViews(int id)
        {
            return CommonDAL.ExecuteNoneQuery("update news set Pageviews=Pageviews+1  where ID=" + id) ? 1 : 0;
        }
        public static NewsInfo FindOneByID(int id)
        {
            DataRow dr = CommonDAL.FindOneByID(id, "[news]");
            return Model(dr);
        }
        public static List<NewsInfo> FindListByIDs(string ids)
        {
            DataTable dt = CommonDAL.FindListByIDs(ids, "[news]");
            return ListModel(dt);
        }
        public static List<NewsInfo> FindPage(string keyword, int startindex, int pagesize, string order, string desc, out int count)
        {
            string filter = "";
            if (!string.IsNullOrEmpty(keyword))
            {
                filter += " and [news].Userid in (select id from [user] where NickName like '%" + keyword + "%')";
            }
            DataTable dt = CommonDAL.FindPage(startindex, pagesize, order, desc, filter, "[news]", out count);
            return ListModel(dt);
        }
        public static List<NewsInfo> FindPage2(string id, int startindex, int pagesize, string order, string desc, out int count)
        {
            string filter = "";
            if (!string.IsNullOrEmpty(id))
            {
                filter += " and [news].Userid =" + id;
            }
            DataTable dt = CommonDAL.FindPage(startindex, pagesize, order, desc, filter, "[news]", out count);
            return ListModel(dt);
        }
        public static List<NewsInfo> FindAll()
        {
            DataTable dt = CommonDAL.GetDataTable(string.Format("select * from [news]"));
            return ListModel(dt);
        }
        private static NewsInfo Model(DataRow dr)
        {
            NewsInfo info = new NewsInfo();
            if (dr != null)
            {
                info.ID = DataConvert.ToInt(dr["ID"]);
                info.Title = DataConvert.ToString(dr["Title"]);
                info.FContent = DataConvert.ToString(dr["FContent"]);
                info.UserID = DataConvert.ToString(dr["UserID"]);
                info.Pageviews = DataConvert.ToInt(dr["Pageviews"]);
                info.Praise = DataConvert.ToInt(dr["Praise"]);
                info.Contact = DataConvert.ToString(dr["Contact"]);
                info.Phone = DataConvert.ToString(dr["Phone"]);
                info.Company = DataConvert.ToString(dr["Company"]);
                info.Province = DataConvert.ToString(dr["Province"]);
                info.City = DataConvert.ToString(dr["City"]);
                info.Area = DataConvert.ToString(dr["Area"]);
                info.CoverImg = Commons.domain+ DataConvert.ToString(dr["CoverImg"]);
                info.CreateDate = DataConvert.ToDateTime(dr["CreateDate"]);
                info.Name = UserDAL.FindOneByID(DataConvert.ToInt(info.UserID)).NickName;
                info.Fcontents = Commons.SubString(Commons.FilterHTML(info.FContent), 50);
            }
            return info;
        }
        private static NewsInfo Model(DataTable dt)
        {
            if (dt.Rows.Count > 0)
            {
                return Model(dt.Rows[0]);
            }
            else
            {
                return new NewsInfo();
            }
        }
        private static List<NewsInfo> ListModel(DataTable dt)
        {
            List<NewsInfo> list = new List<NewsInfo>();
            DataColumnCollection col = dt.Columns;
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Model(row));
            }
            return list;
        }
    }
}

